package com.it2297.model;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import com.it2297.main.App;

public class User {
	private String nric;
	private String name;
	private String address;
	private String Phone;
	private static String password;
	private String gender;
	private String email;
	private String ethnic;
	private Date dob;
	private String status;
	private String countryOfBirth;
	private Timestamp dateRegister;
	private String role;

	public String getNric() {
		return nric;
	}

	public void setNric(String nric) {
		this.nric = nric;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getPhone() {
		return Phone;
	}

	public void setPhone(String phone) {
		this.Phone = phone;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getEthnic() {
		return ethnic;
	}

	public void setEthnic(String ethnic) {
		this.ethnic = ethnic;
	}

	public Date getDob() {
		return dob;
	}

	public void setDob(Date dob) {
		this.dob = dob;
	}
	
	public String getRole() {
		return role;
	}
	
	public void setRole(String role) {
		this.role = role;
	}
	
	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getCountryOfBirth() {
		return countryOfBirth;
	}

	public void setCountryOfBirth(String countryOfBirth) {
		this.countryOfBirth = countryOfBirth;
	}

	public Timestamp getDateRegister() {
		return dateRegister;
	}

	public void setDateRegister(Timestamp dateRegister) {
		this.dateRegister = dateRegister;
	}

	public static User getUser(String nric) {
		User user = new User();

		try {
			App.mySqlConnection.open();

			ResultSet resultSet = App.mySqlConnection
					.executeQuery("SELECT * FROM americano.user WHERE nric = '"
							+ nric + "'");

			while (resultSet.next()) {
				user.setNric(resultSet.getString("nric"));
				user.setName(resultSet.getString("name"));
				user.setRole(resultSet.getString("role"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return user;
	}

	public static boolean isUserExist(String nric) {
		boolean isExist = false;
		
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection().prepareStatement(
							"SELECT * FROM americano.user WHERE nric = ?");
			preparedStatement.setString(1, nric);

			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {
				isExist = true;
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return isExist;
	}

	//this is for the login 
	public User retrieve(String nric , String password) {	
		
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection()
					.prepareStatement(
							"SELECT * FROM americano.user WHERE nric = ?  AND password =?");
			preparedStatement.setString(1, nric);
			preparedStatement.setString(2,  password);

			ResultSet rs = preparedStatement.executeQuery();
			
			if(rs.next()){
				setAttributes(rs);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return this;
	}
	
	public static User retrieveDetails(String nric) {	
		User user=new User();
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection()
					.prepareStatement(
							"SELECT * FROM americano.user WHERE nric = ? ");
			preparedStatement.setString(1, nric);

			ResultSet rs = preparedStatement.executeQuery();
			
			if(rs.next()){
				user.setAddress(rs.getString("address"));
				user.setCountryOfBirth(rs.getString("birth_country"));
				user.setDob(rs.getDate("dob"));
				user.setEmail(rs.getString("email"));
				user.setEthnic(rs.getString("ethnic"));
				user.setGender(rs.getString("gender"));
				user.setName(rs.getString("name"));
				user.setPhone(rs.getString("phone"));
				user.setStatus(rs.getString("status"));
				user.setPassword(rs.getString("password"));
				user.setNric(rs.getString("nric"));
				user.setRole(rs.getString("role"));
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return user;
	}
	
	public static boolean Login(String nric, String password) {	
		boolean valid = false;
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.user WHERE nric = ? AND password =? AND status = 'ava'");
			preparedStatement.setString(1, nric);
			preparedStatement.setString(2, password);

			ResultSet rs = preparedStatement.executeQuery();
			
			if(rs.next()){
				valid = true;
			}else{
				valid = false;
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
			
			
		} finally {
			App.mySqlConnection.close();
		}
		return valid;
	}
	
	
	//this is for the create customer view
	public boolean create(User user) {
		boolean valid = false;
		Date dob=user.getDob();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = new Date();
		
		System.out.println(df.format(date));
		
		
		
	//	SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
		try {
			App.mySqlConnection.open();

			String sql ="INSERT INTO americano.user VALUES('" + user.getNric() + "','" + user.getName() + "'," +
					"'" + user.getEmail() + "','" + user.getAddress() + "','"
			+ user.getCountryOfBirth() + "','" + user.getPhone() + "','" + user.getGender() + "','" + user.getEthnic() +
			"','" + 
			//user.getDob()
			df.format(dob)
			+ "','" + user.getStatus() + "','" + df.format(date) + "','" + user.getRole() + "','" + user.getPassword() + "')";
			System.out.println(sql);
			
			App.mySqlConnection.executeUpdate(sql);
			valid = true;

		} catch (SQLException ex) {
			ex.printStackTrace();
			valid = false;
		} finally {
			App.mySqlConnection.close();
		}
		return valid;
	}

	//this is for the update customer view
	public void update(User user) {

		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection()
					.prepareStatement(
							"UPDATE americano.user SET name = ?, email= ?, address= ?, phone= ?, password= ? WHERE nric = ?");
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getEmail());
			preparedStatement.setString(3, user.getAddress());
			preparedStatement.setString(4, user.getPhone());
			preparedStatement.setString(5, user.getPassword());
			preparedStatement.setString(6, user.getNric());

			preparedStatement.executeUpdate();
			preparedStatement.close();

		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			App.mySqlConnection.close();

		}
	}

	//this is for the delete customer view
	public boolean delete() {

		boolean success = true;
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection().prepareStatement(
							"UPDATE americano.user SET status = 'UNA' WHERE nric = ?");
			preparedStatement.setString(1, nric);

			preparedStatement.executeUpdate();
			preparedStatement.close();
			
			success = true;

		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			App.mySqlConnection.close();

		}
		return success;
	}

	
	public static User view(String nric) {
		
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection().prepareStatement(
							"SELECT * FROM americano.user WHERE nric = ? AND status= 'ava'");
			preparedStatement.setString(1, nric);

			ResultSet rs = preparedStatement.executeQuery();
			if(rs.next()){
				User user = new User();
				user.setAttributes(rs);
			}
		
		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			App.mySqlConnection.close();

		}
		return getUser(null);
	}

	//done with search |BOTTOM|
	public User search() {

		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection().prepareStatement(
							"SELECT * FROM americano.user WHERE nric = ? and role = 'Customer' and status ='ava'");
			preparedStatement.setString(1, nric);

			ResultSet rs = preparedStatement.executeQuery();
			
			if(rs.next()){
				setAttributes(rs);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return this;
	}
	//Do not edit this. |TOP|	

	public static boolean isCustomerExist(String nric){
		
		boolean valid = false;
		//ResultSet rs = null;
		
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.user WHERE nric = ? AND role = 'CUSTOMER' AND status = 'ava'");
			preparedStatement.setString(1, nric);
			
			ResultSet rs = preparedStatement.executeQuery();
			
			if(rs.next()){
				valid = true;
			}else{
				valid = false;
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
			
			
		} finally {
			App.mySqlConnection.close();
		}
		return valid;
	}
	
public static boolean isNricExist(String nric){
		
		boolean valid = false;
		
		try {
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.user WHERE nric = ?  AND status = 'ava'");
			preparedStatement.setString(1, nric);
			
			ResultSet rs = preparedStatement.executeQuery();
			
			if(rs.next()){
				valid = true;
			}else{
				valid = false;
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
			
			
		} finally {
			App.mySqlConnection.close();
		}
		return valid;
	}
		
		
		
	
	
	
	
	
	
	
	
	
	
	
	// Jewel
	// please do not delete.Thank you.
	public static boolean createUser(User user) {

		boolean success = false;

		String nric = user.getNric().toString();
		String name = user.getName().toString();
		String address = user.getAddress().toString();
		String Phone = user.getPhone().toString();
		String password = user.getPassword().toString();
		// char gender = needy.getGender();
		String email = user.getEmail().toString();
		// String ethnic = needy.getEthnic().toString();
		// Date dob = needy.getDob();
		String status = user.getStatus().toString();
		String countryOfBirth = user.getCountryOfBirth().toString();
		// Timestamp dateRegister = needy.getDateRegister();

		try {
			App.mySqlConnection.open();

			// String sql =
			// "INSERT INTO americano.needy(household_income,num_of_dependant,family_Size) VALUES ( '"+
			// nric+ "','"+ name+ "','"+ address+ "','"+ Phone+ "','"+ email+
			// "','"+ status+ "','"+ countryOfBirth + "' )";
			String sql1 = "INSERT INTO americano.user(nric,name,address,phone,password,email,status,birth_country) VALUES ( '"
					+ nric
					+ "','"
					+ name
					+ "','"
					+ address
					+ "','"
					+ Phone
					+ "','"
					+ password
					+ "','"
					+ email
					+ "','"
					+ status
					+ "','"
					+ countryOfBirth + "' )";
			// sql += "VALUES('" + HouseHoldIncome + "','" + NumOfDependent
			// + "','" + FamilySize + "')";

			App.mySqlConnection.executeUpdate(sql1);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return success;

	}
	
	private void setAttributes(ResultSet resultSet){
		try{
			this.setAddress(resultSet.getString("address"));
			this.setCountryOfBirth(resultSet.getString("birth_country"));
			this.setDateRegister(resultSet.getTimestamp("date_register"));
			this.setDob(resultSet.getDate("dob"));
			this.setEmail(resultSet.getString("email"));
			this.setEthnic(resultSet.getString("ethnic"));
			this.setGender(resultSet.getString("gender"));
			this.setName(resultSet.getString("name"));
			this.setNric(resultSet.getString("nric"));
			this.setPhone(resultSet.getString("phone"));
			this.setStatus(resultSet.getString("status"));
		}catch(SQLException e){
			e.printStackTrace();
		}
		
	}

	//Jewel
	//Please do not delete. Thank you.
	public static boolean updateUser(Needy needy) {

		boolean success = false;

		//double HouseHoldIncome = needy.getHouseHoldIncome();
		//int NumOfDependent = needy.getNumOfDependent();
		//int FamilySize = needy.getFamilySize();

		String nric = needy.getNric().toString();
		String name = needy.getName().toString();
		String address = needy.getAddress().toString();
		String Phone = needy.getPhone().toString();
		String password = needy.getPassword().toString();
		// char gender = needy.getGender();
		String email = needy.getEmail().toString();
		 String ethnic = needy.getEthnic().toString();
		// Date dob = needy.getDob();
		String status = needy.getStatus().toString();
		String countryOfBirth = needy.getCountryOfBirth().toString();
		// Timestamp dateRegister = needy.getDateRegister();

		try {
			App.mySqlConnection.open();

			String sql = "UPDATE americano.user set nric= '" + nric + "', name = '" + name + "', address ='"+ address +"', phone ='"+ Phone +"',password ='"+ password +"'email='" +email+"',ethnic='"+ethnic+"',status='"+status+"',birth_country='"+countryOfBirth+ "'WHERE nric = '" + nric + "'";
				
			App.mySqlConnection.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return success;

	}
}
